# _*_ coding:utf-8 _*_
# @File  : position.py
# @Time  : 2021-07-27  15:17
# @Author: zizle
import datetime
import numpy as np

import pandas as pd
from fastapi import APIRouter, Depends, Query
from db import FAConnection
from interfaces.depends import require_start_date, require_end_date
from utils.constant import VARIETY_ZH
from status import r_status

position_api = APIRouter()


@position_api.get('/net-rate/', summary='指定日期区间的净持率分析')
async def analysis_interval_net_position(ds: datetime.datetime = Depends(require_start_date),
                                         de: datetime.datetime = Depends(require_end_date)):
    # 本接口性能问题主要在SQL查询
    sql = 'SELECT a.quotes_ts,a.variety_en,b.variety_name,a.close_price,a.position_price,a.position_volume,' \
          'a.long_position,a.short_position ' \
          'FROM dat_futures_price_position AS a ' \
          'INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
          'WHERE a.variety_en=a.contract AND a.quotes_ts>=%s AND a.quotes_ts<=%s;'
    db = FAConnection(conn_name='查询区间净持率')
    records = db.query(sql, [int(ds.timestamp()), int(de.timestamp())])
    df = pd.DataFrame(records)
    # 计算每行的净持率
    df['pos_rate'] = (df['long_position'] - df['short_position']) / df['position_volume']
    # 按品种分组取最值
    max_min_df = df.groupby(by=['variety_en'], as_index=False)['pos_rate'].agg({'max_rate': 'max', 'min_rate': 'min'})
    # 取最大日期的净持率
    cur_rate_df = df[df['quotes_ts'] == df['quotes_ts'].max()]
    cur_rate_df['net_position'] = cur_rate_df['long_position'] - cur_rate_df['short_position']
    # 横向合并
    ret_df = pd.merge(cur_rate_df, max_min_df, on=['variety_en'], how='left')
    # 计算所在百分比
    ret_df['cur_pos'] = (ret_df['pos_rate'] - ret_df['min_rate']) / (ret_df['max_rate'] - ret_df['min_rate'])
    ret_df['quote_date'] = ret_df['quotes_ts'].apply(lambda x: datetime.datetime.fromtimestamp(x).strftime('%Y-%m-%d'))
    # 计算权重价格、前20净持仓
    ret_df['weight_price'] = ret_df['position_price'] / ret_df['position_volume']
    # 保留数据位数
    ret_df['pos_rate'] = ret_df['pos_rate'].apply(lambda x: round(x * 100, 2))
    ret_df['max_rate'] = ret_df['max_rate'].apply(lambda x: round(x * 100, 2))
    ret_df['min_rate'] = ret_df['min_rate'].apply(lambda x: round(x * 100, 2))
    ret_df['cur_pos'] = ret_df['cur_pos'].apply(lambda x: round(x * 100, 2))
    ret_df['weight_price'] = ret_df['weight_price'].apply(lambda x: round(x, 2))
    ret_df['net_position'] = ret_df['net_position'].apply(lambda x: round(x, 0))
    ret_df.fillna('-', inplace=True)
    rep_data = ret_df.to_dict(orient='records')
    return {'code': r_status.SUCCESS, 'message': '查询净持率区间位成功!', 'data': rep_data}


@position_api.get('/weekly-pp/', summary='指定日期查询周度持仓与权重指数分析')
async def weekly_position_price(date: str = Query(..., min_length=8, max_length=8)):
    # 验证查询日期格式
    try:
        query_date = datetime.datetime.strptime(date, '%Y%m%d')
    except ValueError:
        return {'code': r_status.VALIDATE_ERROR, 'message': '日期格式错误!', 'data': []}

    this_monday = query_date + datetime.timedelta(days=-query_date.weekday())  # 本周一
    current_date = int(query_date.timestamp())  # 请求的日期

    last_monday = int((this_monday + datetime.timedelta(days=-7)).timestamp())  # 上周一
    last_sunday = int((this_monday + datetime.timedelta(days=-1)).timestamp())  # 上周日
    # 获取上周一至当前日期的数据
    db = FAConnection(conn_name='查询周度持仓')
    sql = 'SELECT quotes_ts,variety_en,contract,close_price,position_price,position_volume,long_position,' \
          'short_position ' \
          'FROM dat_futures_price_position WHERE quotes_ts>=%s AND quotes_ts<=%s AND variety_en=contract;'
    records = db.query(sql, param=[last_monday, current_date])
    if not records:
        return {'code': r_status.NOT_CONTENT, 'message': '查询无数据!', 'data': []}
    # 分析数据
    df = pd.DataFrame(records)
    # 01分开上周和本周的数据
    last_week_df = df[(df['quotes_ts'] >= last_monday) & (df['quotes_ts'] <= last_sunday)]
    curr_week_df = df[(df['quotes_ts'] > last_sunday) & (df['quotes_ts'] <= current_date)]
    # 02取上周最后一天与本周最后一天数据进行计算
    last_week_df = last_week_df[last_week_df['quotes_ts'] == last_week_df['quotes_ts'].max()]
    curr_week_df = curr_week_df[curr_week_df['quotes_ts'] == curr_week_df['quotes_ts'].max()]

    # 03修改列名称，拼接数据进行计算
    last_week_df.rename(columns={'quotes_ts': 'l_date', 'close_price': 'l_close_price', 'position_price': 'l_position_price',
                                 'position_volume': 'l_position_volume', 'long_position': 'l_long_position',
                                 'short_position': 'l_short_position'},
                        inplace=True)
    curr_week_df.rename(columns={'quotes_ts': 'c_date', 'close_price': 'c_close_price', 'position_price': 'c_position_price',
                                 'position_volume': 'c_position_volume', 'long_position': 'c_long_position',
                                 'short_position': 'c_short_position'},
                        inplace=True)

    ret_df = pd.merge(last_week_df, curr_week_df, how='inner', on='variety_en')
    # 04计算目标数据
    ret_df['l_position'] = ret_df['l_position_volume']
    ret_df['c_position'] = ret_df['c_position_volume']
    ret_df['position_increase'] = (ret_df['c_position'] - ret_df['l_position']) / ret_df['l_position']
    ret_df['l_price'] = ret_df['l_position_price'] / ret_df['l_position_volume']
    ret_df['c_price'] = ret_df['c_position_price'] / ret_df['c_position_volume']
    ret_df['wp_increase'] = (ret_df['c_price'] - ret_df['l_price']) / ret_df['l_price']
    ret_df = ret_df[['variety_en', 'l_date', 'l_price', 'l_position', 'position_increase',
                     'c_date', 'c_price', 'c_position', 'wp_increase']]
    # 选取不含inf和nan的行
    ret_df = ret_df[~ret_df.isin([np.nan, np.inf]).any(1)]
    ret_df = ret_df.sort_values(by='position_increase', ascending=False)  # 按持仓增减幅度降序
    ret_df['variety_name'] = ret_df['variety_en'].apply(lambda x: VARIETY_ZH.get(x, x))
    ret_df['l_price'] = ret_df['l_price'].apply(lambda x: round(x, 2))
    ret_df['c_price'] = ret_df['c_price'].apply(lambda x: round(x, 2))
    ret_df['position_increase'] = ret_df['position_increase'].apply(lambda x: round(x, 6))
    ret_df['wp_increase'] = ret_df['wp_increase'].apply(lambda x: round(x, 6))
    # 返回数据
    l_date, c_date = ret_df['l_date'].min(), ret_df['c_date'].max()
    l_date = datetime.datetime.fromtimestamp(int(l_date)).strftime('%Y%m%d')
    c_date = datetime.datetime.fromtimestamp(int(c_date)).strftime('%Y%m%d')
    return {'last_date': l_date, 'current_date': c_date, 'data': ret_df.to_dict(orient='records')}
